﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
    /// <summary>
    /// 对*Template*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class TemplateSqlDALProvider:IDAL.IDALTemplate
    { 
        public int Insert(TB.Entity.Template obj)
        {
           //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Template_insert") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@template_idnum",obj.template_idnum==String.Empty?DBNull.Value:(object)obj.template_idnum);
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@sellnum",obj.sellnum==0?DBNull.Value:(object)obj.sellnum);
            cmd.Parameters.AddWithValue("@userVer",obj.userVer==String.Empty?DBNull.Value:(object)obj.userVer);
            cmd.Parameters.AddWithValue("@price",obj.price);
            cmd.Parameters.AddWithValue("@title",obj.title==String.Empty?DBNull.Value:(object)obj.title);
            cmd.Parameters.AddWithValue("@hangye",obj.hangye==String.Empty?DBNull.Value:(object)obj.hangye);
            cmd.Parameters.AddWithValue("@cjTime",obj.cjTime);
            cmd.Parameters.AddWithValue("@updateTime",obj.updateTime);
            cmd.Parameters.AddWithValue("@reserver1",obj.reserver1==String.Empty?DBNull.Value:(object)obj.reserver1);
            cmd.Parameters.AddWithValue("@reserver2",obj.reserver2==String.Empty?DBNull.Value:(object)obj.reserver2);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
           return db.ExecuteNonQuery(cmd);
        }
        
        public int Update(TB.Entity.Template obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Template_update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@template_idnum",obj.template_idnum==String.Empty?DBNull.Value:(object)obj.template_idnum);
            cmd.Parameters.AddWithValue("@nickname",obj.nickname==String.Empty?DBNull.Value:(object)obj.nickname);
            cmd.Parameters.AddWithValue("@sellnum",obj.sellnum==0?DBNull.Value:(object)obj.sellnum);
            cmd.Parameters.AddWithValue("@userVer",obj.userVer==String.Empty?DBNull.Value:(object)obj.userVer);
            cmd.Parameters.AddWithValue("@price",obj.price);
            cmd.Parameters.AddWithValue("@title",obj.title==String.Empty?DBNull.Value:(object)obj.title);
            cmd.Parameters.AddWithValue("@hangye",obj.hangye==String.Empty?DBNull.Value:(object)obj.hangye);
            cmd.Parameters.AddWithValue("@cjTime",obj.cjTime);
            cmd.Parameters.AddWithValue("@updateTime",obj.updateTime);
            cmd.Parameters.AddWithValue("@reserver1",obj.reserver1==String.Empty?DBNull.Value:(object)obj.reserver1);
            cmd.Parameters.AddWithValue("@reserver2",obj.reserver2==String.Empty?DBNull.Value:(object)obj.reserver2);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public int Delete(TB.Entity.Template obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Template_delete") as SqlCommand;
            //3.cmd参数赋值
           cmd.Parameters.AddWithValue("@template_idnum", obj.template_idnum);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public List<TB.Entity.Template> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Template_loadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader =  db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.Template> list = new List<TB.Entity.Template>();
            while (reader.Read())
            {
               list.Add(new TB.Entity.Template()
               {
                    template_idnum=reader["template_idnum"]==DBNull.Value?String.Empty:(string)reader["template_idnum"],
                    nickname=reader["nickname"]==DBNull.Value?String.Empty:(string)reader["nickname"],
                    sellnum=reader["sellnum"]==DBNull.Value?0:(int)reader["sellnum"],
                    userVer=reader["userVer"]==DBNull.Value?String.Empty:(string)reader["userVer"],
                    price=(Double)reader["price"],
                    title=reader["title"]==DBNull.Value?String.Empty:(string)reader["title"],
                    hangye=reader["hangye"]==DBNull.Value?String.Empty:(string)reader["hangye"],
                    cjTime=(string)reader["cjTime"],
                    updateTime = (string)reader["updateTime"],
                    reserver1=reader["reserver1"]==DBNull.Value?String.Empty:(string)reader["reserver1"],
                    reserver2=reader["reserver2"]==DBNull.Value?String.Empty:(string)reader["reserver2"],
               });
           }
           return list;
        }
        
        

        public TB.Entity.Template LoadById(string template_idnum)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_Template_loadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@template_idnum", template_idnum);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.Template entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.Template()
                {
                    template_idnum=reader["template_idnum"]==DBNull.Value?String.Empty:(string)reader["template_idnum"],
                    nickname=reader["nickname"]==DBNull.Value?String.Empty:(string)reader["nickname"],
                    sellnum=reader["sellnum"]==DBNull.Value?0:(int)reader["sellnum"],
                    userVer=reader["userVer"]==DBNull.Value?String.Empty:(string)reader["userVer"],
                    price=(Double)reader["price"],
                    title=reader["title"]==DBNull.Value?String.Empty:(string)reader["title"],
                    hangye=reader["hangye"]==DBNull.Value?String.Empty:(string)reader["hangye"],
                    cjTime=(string)reader["cjTime"],
                    updateTime = (string)reader["updateTime"],
                    reserver1=reader["reserver1"]==DBNull.Value?String.Empty:(string)reader["reserver1"],
                    reserver2=reader["reserver2"]==DBNull.Value?String.Empty:(string)reader["reserver2"],
                };
            }
            return entity;
        }   
    }
}